package com.tlgen.orm.components.sqlXml;

import com.google.common.collect.Lists;
import com.tlgen.orm.model.PageResult;
import com.tlgen.orm.utils.dataSource.DBUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.List;
import java.util.Map;
import java.util.Optional;

import static com.tlgen.orm.constant.SQLScript.*;

@Slf4j
public class SqlExecutor {

    private final JdbcTemplate jdbcTemplate;

    // 使用静态工厂方法确保安全初始化
    public static SqlExecutor create() {
        return new SqlExecutor(DBUtils.get());
    }

    private SqlExecutor(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = Optional.ofNullable(jdbcTemplate)
                .orElseThrow(() -> new IllegalArgumentException("JdbcTemplate cannot be null"));
    }

    public Object executeSql(String sql, Map<String, Object> params, Type returnType) {
        // 判断 SQL 类型
        SqlType sqlType = determineSqlType(sql);

        switch (sqlType) {
            case SELECT:
                return executeSelect(sql, params, returnType);
            case INSERT:
            case UPDATE:
            case DELETE:
                return executeUpdate(sql, params);
            case COUNT:
                return executeCount(sql, params);
            default:
                log.error("不支持的 SQL 类型: " + sql);
                return "";
        }
    }

    private SqlType determineSqlType(String sql) {
        String lowerSql = sql.trim().toLowerCase();

        if (lowerSql.startsWith("select")) {
            // 判断是否为 COUNT 查询
            if (lowerSql.contains("count(") || lowerSql.contains(" count ")) {
                return SqlType.COUNT;
            }
            return SqlType.SELECT;
        } else if (lowerSql.startsWith("insert")) {
            return SqlType.INSERT;
        } else if (lowerSql.startsWith("update")) {
            return SqlType.UPDATE;
        } else if (lowerSql.startsWith("delete")) {
            return SqlType.DELETE;
        }
        return SqlType.UNKNOWN;
    }

    private Object executeSelect(String sql, Map<String, Object> params, Type returnType) {
        // 判断是否是分页查询
        Class<?> returnTypeClass = returnType.getClass();

        // 检查是否为参数化类型（如 R<PageResult>）
        if (returnType instanceof ParameterizedType) {
            ParameterizedType paramType = (ParameterizedType) returnType;
            // 提取泛型参数
            Type[] typeArgs = paramType.getActualTypeArguments();
            returnTypeClass = (Class<?>) typeArgs[0];
        }
        if (returnTypeClass == PageResult.class) {
            return executePagedQuery(sql, params);
        }

        // 普通查询
        return executeBaseQuery(sql, returnTypeClass);
    }

    private Object executePagedQuery(String sql, Map<String, Object> params) {
        int pageNum = Integer.parseInt(String.valueOf(params.get("pageNum")));
        int pageSize = Integer.parseInt(String.valueOf(params.get("pageSize")));
        // 1. 查询总数
        String countSql = SELECT_COUNT_PREFIX + SEPARATOR_OPEN + sql + SEPARATOR_CLOSE + TOTAL_COUNT_ALIAS;
        int total = jdbcTemplate.queryForObject(countSql, Integer.class);

        // 2. 查询数据（添加分页限制）
        String pagedSql = sql + SEPARATOR_SPACE + SEPARATOR_LIMIT
                + SEPARATOR_SPACE + (pageNum - 1) * pageSize + SEPARATOR_COMMA + pageSize;

        // 2. 查询数据（添加分页限制）
        List<?> resultList = jdbcTemplate.queryForList(pagedSql);

        // 3. 返回分页结果
        PageResult<Object> pageResult = PageResult.builder().build();
        pageResult.setTotal(total);
        pageResult.setPageNum(pageNum);
        pageResult.setPageSize(pageSize);
        pageResult.setRecords((List<Object>) resultList);

        return pageResult;
    }

    private Object executeBaseQuery(String sql, Class<?> returnTypeClass) {
        // 判断是否返回List
        if (returnTypeClass == List.class) {
            List<?> resultList = jdbcTemplate.queryForList(sql);
            return Optional.of(resultList).orElse(Lists.newArrayList());
        }
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(returnTypeClass));
    }

    private Object executeUpdate(String sql, Map<String, Object> params) {
        // 判断是否是 INSERT 并需要返回主键
        if (sql.toLowerCase().startsWith("insert")) {
            return executeInsertWithKey(sql, params);
        }

        // 普通更新操作
        return jdbcTemplate.update(sql, params);
    }

    private Object executeInsertWithKey(String sql, Map<String, Object> params) {
        KeyHolder keyHolder = new GeneratedKeyHolder();

        jdbcTemplate.update(connection -> {
            PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            // 设置参数...
            return ps;
        }, keyHolder);

        return keyHolder.getKey();
    }

    private Object executeCount(String sql, Map<String, Object> params) {
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

    private enum SqlType {
        SELECT, INSERT, UPDATE, DELETE, COUNT, UNKNOWN
    }

}